package com.ssy.lingxi.report.dao.order;

import cn.hutool.core.collection.CollectionUtil;
import cn.hutool.core.date.DateUtil;
import cn.hutool.core.util.NumberUtil;
import com.ssy.lingxi.common.constant.order.OrderPayTypeEnum;
import com.ssy.lingxi.common.constant.settle.SettlementTypeEnum;
import com.ssy.lingxi.report.entity.report.PurchaseDayReportDO;
import com.ssy.lingxi.report.entity.settleaccount.PlatformSettlementStrategyMemberDO;
import com.ssy.lingxi.report.model.bo.*;
import com.ssy.lingxi.report.model.bo.tally.statistics.PurchaseOrderStatisticsTallyVO;
import com.ssy.lingxi.report.model.bo.tally.statistics.SaleOrderStatisticsTallyVO;
import com.ssy.lingxi.report.model.vo.convert.MemberOrderSettlementCVO;
import com.ssy.lingxi.report.model.vo.convert.PlatformScoreSettlementDetailCVO;
import com.ssy.lingxi.report.model.vo.convert.PlatformSettlementDetailCVO;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Service;

import java.util.*;
import java.util.stream.Collectors;

/**
 * 订单服务
 * @author 伍将
 * @date 2020/11/3
 * @version 2.0.0
 */
@Service
public class ProcurementOrderDAO {

    private static final Logger logger = LoggerFactory.getLogger(ProcurementOrderDAO.class);


    @Autowired
    @Qualifier("orderJdbcTemplate")
    private JdbcTemplate jdbcTemplate;

    /**
     * 查询已提交的订单
     * @param startTime 开始时间
     * @param limitCount 限制计数
     * @param selectColumns 查询的字段
     * @return 已提交的订单
     */
    private List<MemberOrderBO> getSubmitOrder(Long startTime, Integer limitCount,String selectColumns){
        StringBuilder sql = new StringBuilder();
        sql.append(" SELECT ")
                .append(selectColumns)
                .append(" FROM ord_order ")
                .append(" where outer_status>=2 and outer_status!=6")   // 查询已提交的订单
                .append(" and create_time>=\'" + DateUtil.formatDateTime(new Date(startTime))+"\'")
                .append(" group by member_id,role_id ")
                .append(" order by num desc ")
                .append(" limit " + limitCount);

        return jdbcTemplate.query(sql.toString(), new BeanPropertyRowMapper<>(MemberOrderBO.class));
    }

    /**
     * 获取订单Top会员
     * @author 伍将
     * @date 2020/11/3
     * @param startTime:
     * @param limitCount: 
     * @return java.util.List<com.ssy.lingxi.report.model.vo.response.ActiveMerchantVO>
     **/
    public List<MemberOrderBO> getOrderTopMember(Long startTime, Integer limitCount) {
        return getSubmitOrder(startTime,limitCount," vendor_member_id as member_id,vendor_role_id as role_id,count(*) as num");
    }

    /**
     * 获取订单采购Top会员
     * @author 伍将
     * @date 2020/11/3
     * @param startTime:
     * @param limitCount:
     * @return java.util.List<com.ssy.lingxi.report.model.vo.response.ActiveMerchantVO>
     **/
    public List<MemberOrderBO> getOrderTopPurchaseMember(Long startTime, Integer limitCount) {
        return getSubmitOrder(startTime,limitCount," buyer_member_id as member_id,buyer_role_id as role_id,count(*) as num");
    }

    /**
     * 获取销售订单统计
     * @return 销售订单统计
     */
    public List<SaleOrderStatisticsTallyVO> getSaleOrderStatisticsMain() {
        StringBuilder sql = new StringBuilder();
        sql.append(" select ")
                .append(" vendor_member_id member_id,vendor_role_id role_id,")
                .append(" SUM(CASE WHEN vendor_inner_status = 101 THEN 1 ELSE 0 END) AS to_be_commit_valify_count,")//待提交审核订单
                .append(" SUM(CASE WHEN vendor_inner_status = 103 THEN 1 ELSE 0 END) AS to_Be_valify_step1_count,")//待一级审核数
                .append(" SUM(CASE WHEN vendor_inner_status = 105 THEN 1 ELSE 0 END) AS to_Be_valify_step2_count,")//待二级审核数
                .append(" SUM(CASE WHEN vendor_inner_status in(102,104,106,107) THEN 1 ELSE 0 END) AS to_Be_confirm_count,")//待确认数
                .append(" SUM(0) AS to_be_confirm_pay_result_count,")//待确认支付结果订单
                .append(" SUM(CASE WHEN vendor_inner_status in(111) THEN 1 ELSE 0 END) AS to_be_add_delivery_goods_count,")//待新增销售发货单订单
                .append(" SUM(CASE WHEN vendor_inner_status = 114 THEN 1 ELSE 0 END) AS to_be_add_logistics_count,")//待新增物流单订单
                .append(" SUM(CASE WHEN vendor_inner_status=118  THEN 1 ELSE 0 END) AS to_be_confirm_delivery_goods_count,")//待确认发货订单
                .append(" SUM(CASE WHEN vendor_inner_status =120 THEN 1 ELSE 0 END) AS to_be_confirm_receipt_count,")//待确认回单订单
                .append(" SUM(CASE WHEN vendor_inner_status = 122 THEN 1 ELSE 0 END) AS to_be_archive_count")//待归档订单
                .append(" from ord_order")
                .append(" group by vendor_member_id,vendor_role_id")
                .append(" union")
                .append(" select 0 member_id,0 role_id,") //平台
                .append(" 0 AS to_be_commit_valify_count,0 AS to_Be_valify_step1_count,0 AS to_Be_valify_step2_count,0 AS to_Be_confirm_count,")
                .append(" SUM(CASE WHEN vendor_inner_status = 109 and outer_status = 7 THEN 1 ELSE 0 END) AS to_be_confirm_pay_result_count,")
                .append(" 0 AS to_be_add_delivery_goods_count,0 AS to_be_add_logistics_count,0 AS to_be_confirm_delivery_goods_count,")
                .append(" 0 AS to_be_confirm_receipt_count,0 AS to_be_archive_count")
                .append(" from ord_order;");

        return jdbcTemplate.query(sql.toString(), new BeanPropertyRowMapper<>(SaleOrderStatisticsTallyVO.class));
    }

    public List<SaleOrderStatisticsTallyVO> getSaleOrderEvaluateStatistics() {
        StringBuilder sql = new StringBuilder();
        sql.append(" select")
                .append(" vendor_member_id member_id,vendor_role_id role_id,")
                .append(" COUNT(id) AS toBeEvaluateCount")//待评价订单
                .append(" from ms_mc_member_order_comment")
                .append(" where vendor_complete_comment_status = 0 ")
                .append(" group by vendor_member_id,vendor_role_id;");

        return jdbcTemplate.query(sql.toString(), new BeanPropertyRowMapper<>(SaleOrderStatisticsTallyVO.class));
    }

    public List<SaleOrderStatisticsTallyVO> getSaleOrderToBeConfirmPayResult() {
        StringBuilder sql = new StringBuilder();
        sql.append(" select")
                .append(" member_id,role_id,count(*) to_be_confirm_pay_result_count")
                .append(" from (")
                .append(" select ord.vendor_member_id member_id ,ord.vendor_role_id  role_id")//待评价订单
                .append(" from ord_order ord")
                .append(" left join ord_payment op on ord.id=op.order_id")
                .append(" where ord.vendor_inner_status = 109 ")
                .append(" and ord.outer_status = 7 ")
                .append(" and op.fund_mode =2 ")
                .append(" group by ord.id")
                .append(" ) t")
                .append(" group by member_id,role_id;");

        return jdbcTemplate.query(sql.toString(), new BeanPropertyRowMapper<>(SaleOrderStatisticsTallyVO.class));
    }

    /**
     * 查询销售订单：待评价
     * @return 销售订单：待评价
     */
    public List<SaleOrderStatisticsTallyVO> getSaleOrderStatistics() {
        Map<String, SaleOrderStatisticsTallyVO> map=null;
        List<SaleOrderStatisticsTallyVO> saleOrderStatisticsMain = getSaleOrderStatisticsMain();
        if(CollectionUtil.isNotEmpty(saleOrderStatisticsMain)){
            map = saleOrderStatisticsMain.stream().collect(Collectors.toMap(o -> o.getMemberId() + ":" + o.getRoleId(), o -> o));
        }
        //待评价订单
        List<SaleOrderStatisticsTallyVO> statisticsCount = getSaleOrderEvaluateStatistics();
        if(CollectionUtil.isNotEmpty(statisticsCount)){
            if(map==null){map=new HashMap<>();}
            for(SaleOrderStatisticsTallyVO o :statisticsCount){
                String key=o.getMemberId() + ":" + o.getRoleId();
                SaleOrderStatisticsTallyVO vo = map.get(key);
                if(vo!=null){
                    vo.setToBeEvaluateCount(vo.getToBeEvaluateCount()+o.getToBeEvaluateCount());
                }else{
                    map.put(key,o);
                }
            }
        }
        //待确认支付结果
        List<SaleOrderStatisticsTallyVO> payResult = getSaleOrderToBeConfirmPayResult();
        if(CollectionUtil.isNotEmpty(payResult)){
            if(map==null){map=new HashMap<>();}
            for(SaleOrderStatisticsTallyVO o :payResult){
                String key=o.getMemberId() + ":" + o.getRoleId();
                SaleOrderStatisticsTallyVO vo = map.get(key);
                if(vo!=null){
                    vo.setToBeConfirmPayResultCount(vo.getToBeConfirmPayResultCount()+o.getToBeConfirmPayResultCount());
                }else{
                    map.put(key,o);
                }
            }
        }
        return map!=null? new ArrayList<>(map.values()) :null;
    }

    /***
     * 获取采购订单统计
     * @author 伍将
     * @date 2020/12/2
     * @return 采购订单统计
     **/
    public List<PurchaseOrderStatisticsTallyVO> getPurchaseOrderStatisticsMain() {
        StringBuilder sql = new StringBuilder();
        sql.append(" select")
                .append(" buyer_member_id member_id,buyer_role_id role_id,")
                .append(" SUM(CASE WHEN buyer_inner_status =1 THEN 1 ELSE 0 END) AS toBeCommitValifyCount,")//待提交审核订单
                .append(" SUM(CASE WHEN buyer_inner_status = 2 THEN 1 ELSE 0 END) AS toBeValifyStep1Count,")//待一级审核数
                .append(" SUM(CASE WHEN buyer_inner_status = 4 THEN 1 ELSE 0 END) AS toBeValifyStep2Count,")//待二级审核数
                .append(" SUM(CASE WHEN buyer_inner_status = 6 THEN 1 ELSE 0 END) AS toBeCommitCount,")//待提交数
                .append(" SUM(CASE WHEN buyer_inner_status = 8 THEN 1 ELSE 0 END) AS toBeConfirmContractCount,")//待确认电子合同订单
                .append(" SUM(CASE WHEN (buyer_inner_status = 10 or buyer_inner_status=12 or (buyer_inner_status=11 and outer_status=8)) THEN 1 ELSE 0 END) AS toBePayCount,")//待支付订单
                .append(" SUM(CASE WHEN buyer_inner_status = 14 THEN 1 ELSE 0 END) AS toBeAddStorageCount,")//待新增采购入库单订单
                .append(" SUM(CASE WHEN buyer_inner_status = 16 THEN 1 ELSE 0 END) AS toBeConfirmReceiveGoodsCount,")//待确认收货订单
                .append(" SUM(CASE WHEN buyer_inner_status = 18 THEN 1 ELSE 0 END) AS toBeArchiveCount")//待归档订单
                .append(" from ord_order")
                .append(" group by buyer_member_id,buyer_role_id;");

        return jdbcTemplate.query(sql.toString(), new BeanPropertyRowMapper<>(PurchaseOrderStatisticsTallyVO.class));
    }

    /**
     * 查询采购订单：待评价
     * @return 采购订单：待评价
     */
    public List<PurchaseOrderStatisticsTallyVO> getPurchaseOrderEvaluateStatistics() {
        StringBuilder sql = new StringBuilder();
        sql.append(" select")
                .append(" buyer_member_id member_id,buyer_role_id role_id,")
                .append(" COUNT(id) AS toBeEvaluateCount")//待评价订单
                .append(" from ms_mc_member_order_comment")
                .append(" where buyer_complete_comment_status = 0 ")
                .append(" group by buyer_member_id,buyer_role_id;");

        return jdbcTemplate.query(sql.toString(), new BeanPropertyRowMapper<>(PurchaseOrderStatisticsTallyVO.class));
    }

    public List<PurchaseOrderStatisticsTallyVO> getPurchaseOrderStatistics() {
        Map<String, PurchaseOrderStatisticsTallyVO> map=null;
        List<PurchaseOrderStatisticsTallyVO> saleOrderStatisticsMain = getPurchaseOrderStatisticsMain();
        if(CollectionUtil.isNotEmpty(saleOrderStatisticsMain)){
            map = saleOrderStatisticsMain.stream().collect(Collectors.toMap(o -> o.getMemberId() + ":" + o.getRoleId(), o -> o));
        }
        List<PurchaseOrderStatisticsTallyVO> statisticsCount = getPurchaseOrderEvaluateStatistics();
        if(CollectionUtil.isNotEmpty(statisticsCount)){
            if(map==null){map=new HashMap<>();}
            for(PurchaseOrderStatisticsTallyVO o :statisticsCount){
                String key=o.getMemberId() + ":" + o.getRoleId();
                PurchaseOrderStatisticsTallyVO vo = map.get(key);
                if(vo!=null){
                    vo.setToBeEvaluateCount(vo.getToBeEvaluateCount()+o.getToBeEvaluateCount());
                }else{
                    map.put(key,o);
                }
            }
        }
        return map!=null? new ArrayList<>(map.values()) :null;
    }

    /**
     * 获取积分结算
     * @author 伍将
     * @date 2020/12/14
     * @param startTime:
     * @param endTime:
     * @param memberId:
     * @param roleId:
     * @return 积分结算
     **/
    public PlatformScoreSettlementBO getScoreSettlement(Long startTime, Long endTime, Long memberId, Long roleId) {
        StringBuilder sql = new StringBuilder();
        sql.append(" select ")
                .append(" count(distinct t.id) totalcount,coalesce (sum (t.amount ), 0) totalscore")
                .append(" from(select ord.id as id, pay.pay_amount as amount")
                .append(" from ord_order ord inner join ord_payment pay on ord.id = pay.order_id")
                .append(" where pay.fund_mode=1 ")   // 平台积分
                .append(" and pay.pay_time between \'" + DateUtil.formatDateTime(new Date(startTime)) + "\' and \'" + DateUtil.formatDateTime(new Date(endTime))+"\'")
                 .append(" and ord.order_type in(7,8)")   //订单类型为积分订单
                .append( " and ord.order_type <> 0") //排除无需支付类型
                .append(" and ord.vendor_member_id=" + memberId + " and ord.vendor_role_id=" + roleId)
                .append(" union all")
                .append(" select ord.id as id, ded.used_point as amount")
                .append(" from ord_order as ord inner join ord_payment pay on ord.id = pay.order_id inner join ord_deduction as ded")
                .append(" on ded.order_id = ord.id")
                .append(" where pay.fund_mode=1 ")   // 平台积分
                .append("   and pay.outer_status=100") //确认到账
                .append("   and ord.outer_status <> 101")   // 剔除订单已取消
                .append("   and pay.pay_time between \'" + DateUtil.formatDateTime(new Date(startTime)) + "\' and \'" + DateUtil.formatDateTime(new Date(endTime))+"\'")
                .append( " and ord.order_type <> 0") //排除无需支付类型
                .append( " and ded.type =0")
                .append(" and ord.vendor_member_id=" + memberId + " and ord.vendor_role_id=" + roleId)
                .append(") as t");

        List<PlatformScoreSettlementBO> resultList = jdbcTemplate.query(sql.toString(), new BeanPropertyRowMapper<>(PlatformScoreSettlementBO.class));
        logger.info("平台积分执行查询平台结算汇总sql: {}", sql);
        // 剔除订单数与运费总额全为0的数据
        return resultList.stream().filter(o -> o.getTotalCount() > 0 || o.getTotalScore() > 0).findFirst().orElse(null);
    }

    /**
     * 获取积分结算明细
     * @author 伍将
     * @date 2020/12/14
     * @param startTime:
     * @param endTime:
     * @param memberId:
     * @param roleId:
     * @return 积分结算明细
     **/
    public List<PlatformScoreSettlementDetailCVO> getScoreSettlementDetail(Long startTime, Long endTime, Long memberId, Long roleId) {
        StringBuilder sql = new StringBuilder();
        sql.append(" select ")
                .append(" ord.order_no orderNo,ord.digest orderAbstract,ord.order_type orderType,ord.create_time orderTimeTemp,ord.total_amount orderScore,pay.pay_amount payScore,pay.pay_time payTimeTemp,pay.id dataId, pay.pay_amount settlementAmount ")
                .append(" from ord_order ord")
                .append(" inner join ord_payment pay")
                .append(" on ord.id=pay.order_id")
                .append(" where pay.fund_mode=1 ")   // 平台积分
                .append(" and pay.pay_time between \'" + DateUtil.formatDateTime(new Date(startTime)) + "\' and \'" + DateUtil.formatDateTime(new Date(endTime))+"\'")
                .append(" and ord.order_type in(7,8)")   //订单类型为积分订单
                .append( " and ord.order_type <> 0") //排除无需支付类型
                .append(" and ord.vendor_member_id=" + memberId + " and ord.vendor_role_id=" + roleId)
                .append(" union all")
                .append(" select")
                .append(" ord.order_no orderNo,ord.digest orderAbstract,ord.order_type orderType,ord.create_time orderTimeTemp,ord.total_amount orderScore,ded.used_point payScore,pay.pay_time payTimeTemp,pay.ID dataId, ded.amount settlementAmount")
                .append(" from ord_order as ord inner join ord_payment pay on ord.id = pay.order_id inner join ord_deduction as ded on ded.order_id = ord.id")
                .append(" where pay.fund_mode=1 ")   // 平台积分
                .append("   and pay.outer_status=100") //确认到账
                .append("   and ord.outer_status <> 101")   // 剔除订单已取消
                .append(" and pay.pay_time between \'" + DateUtil.formatDateTime(new Date(startTime)) + "\' and \'" + DateUtil.formatDateTime(new Date(endTime))+"\'")
                .append( " and ord.order_type <> 0") //排除无需支付类型
                .append( " and ded.type = 0 ") //排除无需支付类型
                .append(" and ord.vendor_member_id=" + memberId + " and ord.vendor_role_id=" + roleId);
        logger.info("平台积分结算明细执行sql:{}",sql );
        return jdbcTemplate.query(sql.toString(), new BeanPropertyRowMapper<>(PlatformScoreSettlementDetailCVO.class));
    }

    /**
     * 获取代收账款结算
     * @author 伍将
     * @date 2020/12/14
     * @param startTime:
     * @param endTime:
     * @param memberId:
     * @param roleId:
     * @return 代收账款结算
     **/
    public PlatformSettlementBO getSettlement(Long startTime, Long endTime, Long memberId, Long roleId) {
        StringBuilder sql = new StringBuilder();
        sql.append(" select")
                .append(" count(*) totalCount,COALESCE(sum(collectAmount),0) totalAmount")
                .append(" from(")
                .append("   select")
                .append("     ord.order_no orderNo,ord.digest orderAbstract,ord.order_type orderType,ord.create_time orderTime,ord.total_amount orderAmount,pay.pay_time,")
                .append("     coalesce(pro.tax,false) isHasTax,coalesce(pro.tax_rate,0) taxRate,(pro.amount*pay.pay_rate) collectAmount")
                .append("   from ord_order ord")
                .append("   inner join ord_product pro")
                .append("   on ord.id=pro.order_id")
                .append("   inner join ord_payment pay")
                .append("   on ord.id=pay.order_id")
                .append("   where pay.fund_mode=1 ") //平台代收
                .append("   and pay.outer_status=100") //确认到账
                .append("   and pay.pay_rate>0") //支付比例大于0
                .append("   and pay.confirm_time between \'" + DateUtil.formatDateTime(new Date(startTime)) + "\' and \'" + DateUtil.formatDateTime(new Date(endTime))+"\'") //支付创建时间
                .append("   and ord.order_type in(1,2,3,4,5,6)")   //订单类型为非积分订单
                .append("   and ord.vendor_member_id=" + memberId + " and ord.vendor_role_id=" + roleId)
                .append(" )t")
                .append(" group by orderNo,orderAbstract,orderType,orderTime,orderAmount,pay_time,isHasTax,taxRate;");

        List<PlatformSettlementBO> resultList = jdbcTemplate.query(sql.toString(), new BeanPropertyRowMapper<>(PlatformSettlementBO.class));
        PlatformSettlementBO result=null;
        if(CollectionUtil.isNotEmpty(resultList)) {
            Optional<PlatformSettlementBO> reduce = resultList.stream().reduce((o1, o2) -> {
                o1.setTotalAmount(NumberUtil.add(o1.getTotalAmount() , o2.getTotalAmount()));
                o1.setTotalCount(o1.getTotalCount() + o2.getTotalCount());
                return o1;
            });
            result=reduce.orElse(null);
        }
        return result;
    }

    /**
     * 根据结算支付方式、开始时间、结算时间、会员id、角色id, 从订单中获取符合条件的结算单
     * @param settlementPaymentType 结算支付方式
     * @param startTime 开始时间
     * @param endTime 结束时间
     * @param memberId 会员id
     * @param roleId 角色id
     * @return 从订单中获取符合条件的结算单
     */
    public PlatformSettlementBO getSettlementBak(Integer settlementPaymentType, Long startTime, Long endTime, Long memberId, Long roleId) {
        StringBuilder sql = new StringBuilder();


        // 确认是否是通联支付
        String appendStr = SettlementTypeEnum.ALLIN_PAY.getCode().equals(settlementPaymentType)
                ? " and pay.pay_type = "+ OrderPayTypeEnum.ALLIN_PAY.getCode() : " and pay.pay_type <>" + OrderPayTypeEnum.ALLIN_PAY.getCode() ;

        sql.append(" select")
                .append(" count(*) totalCount,COALESCE(sum(collectAmount),0) totalAmount")
                .append(" from(")
                .append("   select")
                .append("     ord.order_no orderNo,ord.digest orderAbstract,ord.order_type orderType,ord.create_time orderTime,ord.total_amount orderAmount,pay.pay_time,")
                .append("     coalesce(pro.tax,false) isHasTax,coalesce(pro.tax_rate,0) taxRate,(pro.amount*pay.pay_rate) collectAmount")
                .append("   from ord_order ord")
                .append("   inner join ord_product pro")
                .append("   on ord.id=pro.order_id")
                .append("   inner join ord_payment pay")
                .append("   on ord.id=pay.order_id")
                .append("   where pay.fund_mode=1 ") //平台代收
                .append("   and pay.outer_status=100") //确认到账
                .append("   and pay.pay_rate>0") //支付比例大于0
                .append("   and pay.confirm_time between \'" + DateUtil.formatDateTime(new Date(startTime)) + "\' and \'" + DateUtil.formatDateTime(new Date(endTime))+"\'") //支付创建时间
                .append("   and ord.order_type in(1,2,3,4,5,6)")   //订单类型为非积分订单
                .append(appendStr) // 是否通联支付
                .append("   and ord.vendor_member_id=" + memberId + " and ord.vendor_role_id=" + roleId)
                .append(" )t")
                .append(" group by orderNo,orderAbstract,orderType,orderTime,orderAmount,pay_time,isHasTax,taxRate;");

        List<PlatformSettlementBO> resultList = jdbcTemplate.query(sql.toString(), new BeanPropertyRowMapper<>(PlatformSettlementBO.class));
        PlatformSettlementBO result=null;
        if(CollectionUtil.isNotEmpty(resultList)) {
            Optional<PlatformSettlementBO> reduce = resultList.stream().reduce((o1, o2) -> {
                o1.setTotalAmount(NumberUtil.add(o1.getTotalAmount() , o2.getTotalAmount()));
                o1.setTotalCount(o1.getTotalCount() + o2.getTotalCount());
                return o1;
            });
            result=reduce.orElse(null);
        }
        return result;
    }

    /**
     * 获取平台代收账款结算明细
     * @author 伍将
     * @date 2020/12/14
     * @param startTime:
     * @param endTime:
     * @param memberId:
     * @param roleId:
     * @return:
     **/
    public List<PlatformSettlementDetailCVO> getSettlementDetail(Long startTime, Long endTime, Long memberId, Long roleId) {
        StringBuilder sql = new StringBuilder();
        sql.append(" select")
                .append(" orderNo,orderAbstract,orderType,orderTimeTemp,orderAmount,payTimeTemp,isHasTaxTemp,taxRate,sum(collectAmount) collectAmount")
                .append(" from(")
                .append("   select")
                .append("     ord.order_no orderNo,ord.digest orderAbstract,ord.order_type orderType,ord.create_time orderTimeTemp,ord.total_amount orderAmount,pay.confirm_time payTimeTemp,")
                .append("     coalesce(pro.tax,false) isHasTaxTemp,coalesce(pro.tax_rate,0) taxRate,(pro.amount*pay.pay_rate) collectAmount")
                .append("   from ord_order ord")
                .append("   inner join ord_product pro")
                .append("   on ord.id=pro.order_id")
                .append("   inner join ord_payment pay")
                .append("   on ord.id=pay.order_id")
                .append("   where pay.fund_mode=1 ") //平台代收
                .append("   and pay.outer_status=100") //确认到账
                .append("   and pay.pay_rate>0") //支付比例大于0
                .append("   and pay.confirm_time between \'" + DateUtil.formatDateTime(new Date(startTime)) + "\' and \'" + DateUtil.formatDateTime(new Date(endTime)) + "\'") //支付创建时间
                .append("   and ord.order_type in(1,2,3,4,5,6)")   //订单类型为非积分订单
                .append("   and ord.vendor_member_id=" + memberId + " and ord.vendor_role_id=" + roleId)
                .append(" )t")
                .append(" group by orderNo,orderAbstract,orderType,orderTimeTemp,orderAmount,payTimeTemp,isHasTaxTemp,taxRate;");

        return jdbcTemplate.query(sql.toString(), new BeanPropertyRowMapper<>(PlatformSettlementDetailCVO.class));
    }

    /**
     * 从订单中获取符合条件平台代收账款结算明细
     *
     * @param settlementPaymentType 结算支付策略
     * @param startTime 开始时间
     * @param endTime 结束时间
     * @param memberId 会员id
     * @param roleId 角色id
     * @return 平台代收账款结算明细
     */
    public List<PlatformSettlementDetailCVO> getSettlementDetailBak(Integer settlementPaymentType, Long startTime, Long endTime, Long memberId, Long roleId) {
        StringBuilder sql = new StringBuilder();
        // 确认是否是通联支付
        String appendStr = SettlementTypeEnum.ALLIN_PAY.getCode().equals(settlementPaymentType)
                ? "   and pay.pay_type = "+ OrderPayTypeEnum.ALLIN_PAY.getCode() : "   and pay.pay_type <>" + OrderPayTypeEnum.ALLIN_PAY.getCode() ;

        sql.append(" select")
                .append(" orderNo,orderAbstract,orderType,orderTimeTemp,orderAmount,payTimeTemp,isHasTaxTemp,taxRate,paymentId,paymentTradeNo,sum(collectAmount) collectAmount")
                .append(" from(")
                .append("   select")
                .append("     ord.order_no orderNo,ord.digest orderAbstract,ord.order_type orderType,ord.create_time orderTimeTemp,ord.total_amount orderAmount,pay.confirm_time payTimeTemp,")
                .append("     coalesce(pro.tax,false) isHasTaxTemp,coalesce(pro.tax_rate,0) taxRate,(pro.amount*pay.pay_rate) collectAmount,pay.id paymentId, pay.trade_no paymentTradeNo")
                .append("   from ord_order ord")
                .append("   inner join ord_product pro")
                .append("   on ord.id=pro.order_id")
                .append("   inner join ord_payment pay")
                .append("   on ord.id=pay.order_id")
                .append("   where pay.fund_mode=1 ") //平台代收
                .append("   and pay.outer_status=100") //确认到账
                .append("   and pay.pay_rate>0") //支付比例大于0
                .append("   and pay.confirm_time between \'" + DateUtil.formatDateTime(new Date(startTime)) + "\' and \'" + DateUtil.formatDateTime(new Date(endTime)) + "\'") //支付创建时间
                .append("   and ord.order_type in(1,2,3,4,5,6)")   //订单类型为非积分订单
                .append(appendStr)
                .append("   and ord.vendor_member_id=" + memberId + " and ord.vendor_role_id=" + roleId)
                .append(" )t")
                .append(" group by orderNo,orderAbstract,orderType,orderTimeTemp,orderAmount,payTimeTemp,isHasTaxTemp,taxRate,paymentId,paymentTradeNo;");

        return jdbcTemplate.query(sql.toString(), new BeanPropertyRowMapper<>(PlatformSettlementDetailCVO.class));

    }
    /**
     * 获得指定时间范围可结算的会员集合
     * @param startTime 开始时间
     * @param endTime 结算时间
     * @return 可结算的会员集合
     */
    public List<PlatformSettlementStrategyMemberDO> getSettleMemberListByTime(Long startTime, Long endTime){
        StringBuilder sql = new StringBuilder();
        sql.append(" select")
                .append("   ord.vendor_member_id member_id,ord.vendor_role_id role_id")
                .append("   from ord_order ord")
                .append("   inner join ord_product pro")
                .append("   on ord.id=pro.order_id")
                .append("   inner join ord_payment pay")
                .append("   on ord.id=pay.order_id")
                .append("   where pay.fund_mode=1 ") //平台代收
                .append("   and pay.outer_status=100") //确认到账
                .append("   and pay.pay_time between \'" + DateUtil.formatDateTime(new Date(startTime)) + "\' and \'" + DateUtil.formatDateTime(new Date(endTime)) + "\'") //支付创建时间
                .append("   and ord.order_type in(1,2,3,4,5,6)")   //订单类型为非积分订单
                .append("   group by ord.vendor_member_id,ord.vendor_role_id;");

        return jdbcTemplate.query(sql.toString(), new BeanPropertyRowMapper<>(PlatformSettlementStrategyMemberDO.class));

    }

    /**
     * 分页查询销售单票据报表
     * @author 伍将
     * @date 2020/12/16
     * @param index:
     * @param count:
     * @return 询销售单票据报表
     **/
    public List<MemberReceiptInvoiceBO> pageInvoiceReport(Integer index, Integer count) {
        StringBuilder sql = new StringBuilder();
        sql.append(" select")
                .append(" 	member_id,role_id,order_no,order_abstract,settlement_order_type,order_type,")
                .append(" 	order_time,order_amount,batch,pay_node,pay_time,invoice_status, pay_status,data_id,")
                .append(" 	payMemberId,payRoleId,isHasTax isHasTaxTemp,taxRate,sum(pay_amount) pay_amount")
                .append(" from (")
                .append(" 	select")
                .append(" 		ord.vendor_member_id member_id,ord.vendor_role_id role_id ,ord.order_no order_no,ord.digest order_abstract,'3' settlement_order_type,")
                .append(" 		ord.order_type order_type,ord.create_time order_time,ord.total_amount order_amount,pay.batch_no batch,pay.pay_node,")
                .append(" 		pay.pay_time pay_time,'0' invoice_status, CASE WHEN pay.outer_status in (7,100) THEN 1 ELSE 0 end pay_status,pay.id data_id,")
                .append(" 		ord.buyer_member_id payMemberId,ord.buyer_role_id payRoleId,")
                .append(" 		coalesce(pro.tax,false) isHasTax,coalesce(pro.tax_rate,0) taxRate,(pro.amount*pay.pay_rate) pay_amount")
                .append(" 	from ord_order ord")
                .append(" 	inner join ord_payment pay")
                .append(" 	on ord.id=pay.order_id")
                .append(" 	inner join ord_product pro")
                .append(" 	on ord.id=pro.order_id")
                .append(" 	inner join ord_invoice inv")
                .append(" 	on ord.id=inv.order_id")  // 只查询已开票的数据
                .append(" 	where ord.outer_status>=3   ")  // 已确认过的订单
                .append(" 	and pay.batch_no notnull")     // 剔除支付次数错乱的数据
                .append("   and pay.pay_rate>0") //支付比例大于0
                .append("   limit " + count + " offset " + index)  //分页查询
                .append(" )t")
                .append(" group by member_id,role_id,order_no,order_abstract,settlement_order_type,order_type,")
                .append(" 		order_time,order_amount,batch,pay_node,pay_time,invoice_status,pay_status,data_id,")
                .append(" 		payMemberId,payRoleId,isHasTax,taxRate;");

        return jdbcTemplate.query(sql.toString(), new BeanPropertyRowMapper<>(MemberReceiptInvoiceBO.class));
    }

    /**
     * 查询订单结算数据
     * @author ds
     * @date 2021/3/30
     * @param startTime: 开始时间
     * @param endTime: 结束时间
     * @param payMemberId: 支付会员id
     * @param payRoleId: 支付会员角色id
     * @param memberId: 收款会员id
     * @param roleId: 收款会员角色id
     * @param payType 5：账期，6：月结
     * @return 订单结算数据
     */
    public MemberSettlementBO getOrderSettlementByPurchase(Long startTime, Long endTime, Long payMemberId, Long payRoleId, Long memberId, Long roleId,Integer payType) {
        StringBuilder sql = new StringBuilder();
        sql.append(" select")
                .append(" count(distinct orderNo) totalCount,COALESCE(sum(collectAmount),0) totalAmount")
                .append(" from(")
                .append("   select")
                .append("     ord.order_no orderNo,ord.digest orderAbstract,ord.order_type orderType,ord.create_time orderTime,ord.total_amount orderAmount,pay.pay_time,")
                .append("     coalesce(pro.tax,false) isHasTax,coalesce(pro.tax_rate,0) taxRate,pay.pay_amount collectAmount")
                .append("   from ord_order ord")
                .append("   inner join ord_product pro")
                .append("   on ord.id=pro.order_id")
                .append("   inner join ord_payment pay")
                .append("   on ord.id=pay.order_id")
                .append("   where pay.outer_status=100 ")
                .append("   and pay.pay_channel ="+payType)     // 支付方式（账期或月结）
                .append("   and pay.pay_time between \'" + DateUtil.formatDateTime(new Date(startTime)) + "\' and \'" + DateUtil.formatDateTime(new Date(endTime)) +"\'")
                .append("   and ord.order_type in(1,2,3,4,5,6)")   //订单类型为非积分订单
                .append("   and ord.buyer_member_id=" + payMemberId + " and ord.buyer_role_id=" + payRoleId)    // 付款方
                .append("   and ord.vendor_member_id=" + memberId + " and ord.vendor_role_id=" + roleId)   // 收款方
                .append(" )t");

        List<MemberSettlementBO> resultList = jdbcTemplate.query(sql.toString(), new BeanPropertyRowMapper<>(MemberSettlementBO.class));

        // 剔除订单数与支付总额全为0的数据
        return resultList.stream().filter(o -> o.getTotalCount() > 0 || o.getTotalAmount() > 0).findFirst().orElse(null);
    }

    /**
     * 订单结算明细数据
     * @author ds
     * @date 2021/3/30
     * @param startTime: 开始时间
     * @param endTime: 结束时间
     * @param payMemberId: 支付会员id
     * @param payRoleId: 支付会员角色id
     * @param memberId: 收款会员id
     * @param roleId: 收款会员角色id
     * @param payType 5：账期，6：月结
     * @return 订单结算明细数据
     */
    public List<MemberOrderSettlementCVO> getOrderSettlementDetailByPurchase(Long startTime, Long endTime, Long payMemberId, Long payRoleId, Long memberId, Long roleId, Integer payType) {
        StringBuilder sql = new StringBuilder();
        sql.append(" select")
                .append(" orderNo,orderAbstract,type,orderTimeTemp,orderAmount,payTimeTemp,isHasTaxTemp,taxRate,sum(collectAmount) payAmount,dataId,payCount")
                .append(" from(")
                .append("   select")
                .append("     ord.order_no orderNo,ord.digest orderAbstract,ord.order_type as type,ord.create_time orderTimeTemp,ord.total_amount orderAmount,pay.pay_time payTimeTemp,")
                .append("     coalesce(pro.tax,false) isHasTaxTemp,coalesce(pro.tax_rate,0) taxRate,pay.pay_amount collectAmount,pay.id dataId,pay.batch_no payCount")
                .append("   from ord_order ord")
                .append("   inner join ord_product pro")
                .append("   on ord.id=pro.order_id")
                .append("   inner join ord_payment pay")
                .append("   on ord.id=pay.order_id")
                .append("   where pay.outer_status=100")
                .append("   and pay.pay_channel ="+payType)     // 支付方式（账期或月结）
                .append("   and pay.pay_time between \'" + DateUtil.formatDateTime(new Date(startTime)) + "\' and \'" + DateUtil.formatDateTime(new Date(endTime))+"\'")
                .append("   and ord.order_type in(1,2,3,4,5,6)")   //订单类型为非积分订单
                .append("   and ord.buyer_member_id=" + payMemberId + " and ord.buyer_role_id=" + payRoleId)    // 付款方
                .append("   and ord.vendor_member_id=" + memberId + " and ord.vendor_role_id=" + roleId)   // 收款方
                .append(" )t")
                .append(" group by orderNo,orderAbstract,type,orderTimeTemp,orderAmount,payTimeTemp,isHasTaxTemp,taxRate,dataId,payCount;");

        return jdbcTemplate.query(sql.toString(), new BeanPropertyRowMapper<>(MemberOrderSettlementCVO.class));
    }

    public List<PurchaseDayReportDO> getDayOrderStatistics(Long startTime, Long endTime) {
        StringBuilder sql = new StringBuilder();
        sql.append(" SELECT ")
                .append(" ord.buyer_member_id as member_id,ord.buyer_role_id as role_id,count(*) as num,sum(ord.product_amount) as amount")
                .append(" from ord_order ord")
                .append(" where ord.submit_time>=\'" + DateUtil.formatDateTime(new Date(startTime))+"\'") // 查询已提交的订单
                .append(" and ord.submit_time<=\'" + DateUtil.formatDateTime(new Date(endTime))+"\'")
                .append(" group by ord.buyer_member_id,ord.buyer_role_id;");
        return jdbcTemplate.query(sql.toString(), new BeanPropertyRowMapper<>(PurchaseDayReportDO.class));
    }

    /**
     * 获得指定时间范围可积分结算的会员集合
     * @param startTime 开始时间
     * @param endTime 结算时间
     * @return 可结算的会员集合
     */
    public List<PlatformSettlementStrategyMemberDO> getScoreSettleMemberListByTime(long startTime, long endTime) {
        StringBuilder sql = new StringBuilder();
        sql.append(" select")
                .append("   ord.vendor_member_id member_id,ord.vendor_role_id role_id")
                .append("   from ord_order ord")
                .append("   inner join ord_payment pay")
                .append("   on ord.id=pay.order_id")
                .append("   left join ord_deduction ded")
                .append("   on ord.id=ded.order_id")
                .append("   where pay.fund_mode=1 ") //平台代收
                .append("   and pay.outer_status=100") //确认到账
                .append("   and pay.pay_time between \'" + DateUtil.formatDateTime(new Date(startTime)) + "\' and \'" + DateUtil.formatDateTime(new Date(endTime)) + "\'") //支付创建时间
                .append("   and (ord.order_type in ( 7, 8 ) or ded.id is not null)")   //订单类型为非积分订单
                .append("   group by ord.vendor_member_id,ord.vendor_role_id;");

        return jdbcTemplate.query(sql.toString(), new BeanPropertyRowMapper<>(PlatformSettlementStrategyMemberDO.class));

    }
}
